const {db} = require('../../config/dbconfig')
const {utcDate} = require("../../utils/time");

exports.getArticlesCount = async (req, res, next) => {
    const sql = 'SELECT COUNT(*) as total FROM Articles';
    db.query(sql, (error, results) => {
        if (error) {
            console.error('Error fetching article count:', error);
            return res.status(500).json({error: 'Error fetching article count'});
        }

        const totalArticles = results[0].total;
        res.cc({totalArticles});
    });
}
exports.getCommentsCount = async (req, res, next) => {
    const sql = 'SELECT COUNT(*) from Comments'
    db.query(sql, (error, results) => {
        if (error) {
            console.error('Error fetching comments count:', error);
            return res.status(500).json({error: 'Error fetching comments count'});
        }
        const totalComments = results[0]['COUNT(*)'];
        res.cc({totalComments});
    })
}
exports.getAllUserCount = async (req, res, next) => {
    console.log('get all user count')
    const sql = 'SELECT  COUNT(*) FROM Users';
    db.query(sql, (error, results) => {
        if (error) {
            console.error('Error get users:', error);
            return res.status(500).json({error: '查询失败'});
        }
        res.cc({count: results[0]['COUNT(*)']});
    })
}
exports.countIPAddress = async (req, res, next) => {
    const sql = 'SELECT SUM(count) as count FROM ip_addresses'
    db.query(sql, (err, result) => {
        if (err) return res.cc(err)
        res.cc({
            data: result[0].count
        })
    })
}

exports.getIpAddressList = async (req, res, next) => {
    const {page, limit} = req.query
    const sql = `SELECT * FROM ip_addresses ORDER BY id DESC LIMIT ${(page - 1) * limit}, ${limit}`
    db.query(sql, (err, result) => {
        if (err) return res.cc(err)
        res.send({
            status: 0,
            message: "ok",
            data: result
        })
    })
}
exports.getIpAddressList2 = async (req, res, next) => {
    const sql = `SELECT * FROM ip_addresses ORDER BY id`
    db.query(sql, (err, result) => {
        if (err) return res.cc(err)
        res.send({
            status: 0,
            message: "ok",
            data: result
        })
    })
}

exports.getIpAddressListByCity = async (req, res, next) => {
    const sql = `SELECT 
    CASE 
        WHEN province NOT LIKE '%省' THEN '国外'
        ELSE province 
    END AS province,
    SUM(count) AS total_count
FROM ip_addresses
GROUP BY 
    CASE 
        WHEN province NOT LIKE '%省' THEN '国外'
        ELSE province 
    END;
`
    db.query(sql, (err, result) => {
        if (err) return res.cc(err)
        res.send({
            status: 0,
            message: "ok",
            data: result
        })
    })
}

exports.getIpAddressListByDate = async (req, res, next) => {
    const sql = `SELECT DATE(update_time) AS date, SUM(count) AS total_count
                        FROM ip_addresses
                        GROUP BY DATE(update_time)
                        ORDER BY date;`

    db.query(sql, (err, result) => {
        const convertedData = result.map(item => {
            return {data: utcDate(item.date).slice(0, 10), total_count: item.total_count}
        })
        if (err) return res.cc(err)
        res.send({
            status: 0,
            message: "ok",
            data: convertedData
        })
    })
}
exports.getIpAddressListByYear = async (req, res, next) => {
    const sql = `SELECT DATE_FORMAT(update_time, '%Y') AS year, SUM(count) AS total_count
FROM ip_addresses
GROUP BY DATE_FORMAT(update_time, '%Y')
ORDER BY year;
`
    db.query(sql, (err, result) => {
        if (err) return res.cc(err)
        const convertedData = result.map(item => {
            return {data: item.year, total_count: item.total_count}
        })

        res.send({
            status: 0,
            message: "ok",
            data: convertedData
        })
    })
}
exports.getIpAddressListByMonth = async (req, res, next) => {
    const sql = `SELECT DATE_FORMAT(update_time, '%Y-%m') AS month, SUM(count) AS total_count
FROM ip_addresses
GROUP BY DATE_FORMAT(update_time, '%Y-%m')
ORDER BY month;
`
    db.query(sql, (err, result) => {
        if (err) return res.cc(err)
        const convertedData = result.map(item => {
            return {data: item.month, total_count: item.total_count}
        })
        res.send({
            status: 0,
            message: "ok",
            data: convertedData
        })
    })

}

exports.getCommentlikesCount = async (req, res, next) => {
    const sql = 'SELECT COUNT(*) as count FROM CommentLikes';
    db.query(sql, (error, results) => {
        if (error) {
            console.error('Error fetching comment likes count:', error);
            return res.status(500).json({error: 'Error fetching comment likes count'});
        }

        res.cc({count: results[0].count});
    });
}